﻿#!/usr/bin/perl

use warnings; 
use strict; 

sub GenOMesSQLInquireCmdFile{

	my $path = "../"; 
	my @fileNames; 
	my @meaInfo;
	
	opendir (DIR, $path) or die "can't open it:$!"; 
	@fileNames = readdir DIR;
	closedir(DIR);
	foreach my $file (@fileNames) { 
		print "******file: $file\n";
		my $pos = index($file, ".xml");
		unless ($pos == -1){
			my $str = substr($file, 0, $pos);
			my @list = split(/-/, $str);
			pop(@list);
			my $head = pop(@list);
			
			push(@meaInfo, $head);
		}
	}

	open(OutFile,"<nokrww_table.txt") || die("Can not open the file!$!n");
	my @line = <OutFile>;
	close(OutFile);
	
	my @sqlCmd;
	
	# @meaInfo = ("M1021_IUPC", "M1018_RCPMUEQ", "M5107_FATMVP");
	foreach my $meaMid (@meaInfo){
		print "******meaMid: $meaMid\n";
		my @list = split(/_/, $meaMid);
		my $mid = pop(@list);
		
		foreach my $table(@line){
			
			my $pos = index( $table, "_".$mid."_");
			if($pos != -1){
				$table =~ s/^\s+//;  # romove leading whitespace
				$table =~ s/\s+$//;  # romove trailing whitespace

				my $cmd = "SELECT * FROM NOKRWWRAW.".$table." WHERE RNC_ID='121212000000000063' ORDER BY PERIOD_START_TIME DESC;";
				$cmd = sprintf("%-120s", $cmd);
				my $tail = " --- ".$meaMid;
				push(@sqlCmd, $cmd.$tail);
			}
		}
	}
	
	open(InFile,">sqlCmd.txt") || die("Can not open the file!$!n");
	foreach my $line ( @sqlCmd ) {
		# Add the line to the file
		print InFile $line."\n";
	}
	close(InFile);
	
	print "******Generate sql command file: sqlCmd.txt\n";
}

GenOMesSQLInquireCmdFile;